package cn.com.callback


import cn.com.elite.EliteBean
import org.apache.commons.logging.Log
import org.apache.commons.logging.LogFactory
import org.json.JSONException

import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse

/**
 *
 * @Title: Ys_Workorderid
 * @Description: 华盛通讯，外拨未完结的工单查询上一次时间是否超时
 * @Description: 华盛通讯，统计 邀约人
 * @author: 匡涛* @date 2021-7-5 12:50
 *
 */

public class apiHSTOBremind implements ServiceBackInvoke {
    private static final Log log = LogFactory.getLog(apiHSTOBremind.class);

    public String doCallBack(EliteBean eliteBean, HttpServletRequest request, HttpServletResponse response) {

        try {
            //查询ACW状态超过五分钟的坐席
            /*    def acwfivequery=eliteBean.chaxun_sql("select distinct s.staff_id,s.DEFAULTROLEGROUP  from t_my_groupagent_state t left join staff  s  on s.EXTENSION=t.number where now() >SUBDATE(statettime,interval -5 minute ) and statedesc='整理'", "dataSource");
                for (int b = 0;b<acwfivequery.size();b++){
                    def staffid=acwfivequery.get(b).get("staff_id");
                    def DEFAULTROLEGROUP=acwfivequery.get(b).get("DEFAULTROLEGROUP");

                    if(staffid!=""&&DEFAULTROLEGROUP!=""){
                        def updatesamp = eliteBean.update("INSERT INTO hst_leadsnotice(noticeId, noticeGroup, noticeBy, createGroup, createBy, noticeContent, createddate, limitCount, loadCount, noticeType) VALUES (uuid(), '"+DEFAULTROLEGROUP+"', '"+staffid+"', 'SYSTEM', 'SELITE', '您整理已经超过5分钟,请及时电话就绪哟!', NOW(), '1', '0', '12');", "dataSource");
                    }

                }*/

            //统计邀约人
            /*
            def updateInvitee_count= 	eliteBean.update("UPDATE customer du,(SELECT 	c1.customer_id, count( c2.customer_id ) c2count  FROM customer c1 LEFT JOIN customer c2 ON c1.customer_Id = c2.sq_5  AND c2.sq_5 IS NOT NULL  WHERE"+
                    "  c2.sq_5 IS NOT NULL  AND c1.customer_id != ''  GROUP BY c1.customer_id  ) b  SET du.Invitee_count = b.c2count  WHERE du.customer_id = b.customer_id and  b.c2count>0","dataSource");
            log.debug("邀请人是否成功：" + updateInvitee_count);
            */
            //1.处理时间差导致的入金状态
            try {
                def updatecustomer_deposit = eliteBean.update("update customer c  LEFT JOIN (select  c.customerid,c.depositstatus  from (select  customerId,max(rec_id) as rec_id from  account_information_deposit  where createtime>DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s') group by customerId )  a   LEFT JOIN account_information_deposit c  on a.rec_id=c.rec_id and c.createtime>DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s')   LEFT JOIN customer d  on c.customerId=d.customer_Id  where c.depositstatus!=d.depositstatus) t  on t.customerid=c.customer_id  set c.depositstatus=t.depositstatus  where t.depositstatus is  not  null","dataSource");
                log.debug("处理时间差导致的入金状态是否成功：" + updatecustomer_deposit);
            }catch (Exception e) {
                log.error("处理时间差导致的入金状态异常，失败：" + e.getMessage());
            }

            //2.Esop工单 待办工单提醒 +未读邮件更新 左下角系统提示
            try {
                String[] data=new String[3];
                //2.0先删除
                data[0] = "delete from staffworkload where REPORTTYPE in (7,8)";
                //2.1 写入待办工单提醒
                data[1] = "INSERT INTO staffworkload (ROLEGROUP_ID, STAFF_ID, REPORTTYPE, WORKLOAD, EXPIREWORKLOAD, CHECKTIME) " +
                        "select w.rolegroup_id, w.staff_id,'7',count(hw.workorderno),'0',NOW() " +
                        "from  hst_workorder hw " +
                        "left join  hst_workorderaudit w  on hw.workorder_guid = w.workorder_guid and hw.node_guid = w.node_guid " +
                        "where 1=1 " +
                        "and hw.createdate>DATE_SUB(CURDATE(), INTERVAL 3 MONTH) " +
                        "and hw.workorderstatus='OPEN' " +
                        "and w.staff_id is not null " +
                        "and w.rolegroup_id is not null " +
                        "GROUP BY w.rolegroup_id, w.staff_id ";
                //2.2 写入未读工单提醒
                data[2] = "INSERT INTO staffworkload (ROLEGROUP_ID, STAFF_ID, REPORTTYPE, WORKLOAD, EXPIREWORKLOAD, CHECKTIME) " +
                        "select s.DEFAULTROLEGROUP,s.staff_id,'8',sum(case when hwr.readtime is null then 1 else 0 end),'0',NOW() " +
                        "from documentin d " +
                        "LEFT JOIN hst_email_config h on (FIND_IN_SET(h.urladdress,REPLACE(LOWER(d.documentto), ';', ',')) or FIND_IN_SET(h.sslport,REPLACE(LOWER(d.documentto), ';', ',')) or FIND_IN_SET(h.urladdress,REPLACE(LOWER(d.DocumentCC), ';', ',')) or FIND_IN_SET(h.sslport,REPLACE(LOWER(d.DocumentCC), ';', ','))) " +
                        "LEFT JOIN staff s on Find_In_Set(s.staff_id,h.staff_id) " +
                        "LEFT JOIN hst_workorder_readlog hwr on hwr.documentin_guid=d.documentin_guid and hwr.staff_id=s.staff_id " +
                        "where 1=1 " +
                        "and d.RECEIVEDDATETIME>DATE_SUB(CURDATE(), INTERVAL 3 MONTH)  and h.status=1 " +
                        "GROUP BY s.DEFAULTROLEGROUP,s.staff_id ";
                log.debug("data 处理SQL:" + data.toString());
                int[] dataresult = eliteBean.updateBatch(data, "dataSource");
                log.debug("data 处理SQL结果:" + dataresult.toString());

            }catch (Exception e) {
                log.error("处理时Esop工单 待办工单提醒 +未读邮件更新 左下角系统提示 异常，失败：" + e.getMessage());
            }

            //3.查询现在有多少个需要提醒的外拨任务
            try {
                def repeatresult = eliteBean.chaxun_sql("select  count( distinct o.objective_guid) as countobj from objective o left join customer c  on o.CUSTOMER_GUID=c.CUSTOMER_GUID left join hst_limit_remind hlr on   Find_In_Set(STRINGFIELD13,hlr.codename) > 0 left join elitetask e on e.OBJECTIVE_GUID=o.OBJECTIVE_GUID left join HST_LEADSNOTICE hl  on o.OBJECTIVE_GUID=hl.OBJECTIVE_GUID where 1=1 and o.OBJECTIVESTATUS='open'  and hlr.state=1  and (date_add(LastCallTime, interval hlr.limitdate hour)<=NOW()) and loadCount is null and  str_to_date( o.MODIFIEDDATE, '%Y-%m-%d' )>= date_sub( curdate(), INTERVAL 1 MONTH )   ", "dataSource");
                def countobj = repeatresult.get(0).get("countobj");
                def count = repeatresult.size();
                log.debug("查询现在有多少个需要提醒的外拨任务：" + repeatresult);
                if (countobj > 0) {
                    def apirepeatresult = eliteBean.chaxun_sql("select distinct o.objective_guid,o.HANDLEGROUP_ID, o.HANDLEBY_ID from objective o left join customer c  on o.CUSTOMER_GUID=c.CUSTOMER_GUID left join hst_limit_remind hlr on   Find_In_Set(STRINGFIELD13,hlr.codename) > 0 left join elitetask e on e.OBJECTIVE_GUID=o.OBJECTIVE_GUID left join HST_LEADSNOTICE hl  on o.OBJECTIVE_GUID=hl.OBJECTIVE_GUID where 1=1 and o.OBJECTIVESTATUS='open'  and hlr.state=1  and (date_add(LastCallTime, interval hlr.limitdate hour)<=NOW()) and loadCount is null and  str_to_date( o.MODIFIEDDATE, '%Y-%m-%d' )>= date_sub( curdate(), INTERVAL 1 MONTH )  ", "dataSource");
                    for (int i = 0; i < apirepeatresult.size(); i++) {
                        def objective_guid = apirepeatresult.get(i).get("objective_guid");
                        def HANDLEGROUPID = apirepeatresult.get(i).get("HANDLEGROUP_ID");
                        def HANDLEBYID = apirepeatresult.get(i).get("HANDLEBY_ID");
                        def updatesamp = eliteBean.update(" insert into HST_LEADSNOTICE(noticeId,noticeGroup,noticeBy,createGroup,createBy,noticeContent,createddate,limitCount,loadCount,noticeType,objective_guid) values(UUID(),'" + HANDLEGROUPID + "','" + HANDLEBYID + "','SYSTEM','SELITE','您好,您有外拨任务已经超过了时限了,快去查看吧！',NOW(),2,1,8,'"+objective_guid+"') ", "dataSource");
                        log.debug("objective_guid：" + objective_guid);
                        log.debug("objective_guid：" + i);
                        //	break;
                    }
                } else {
                    return "{\"code\":\"0\",\"message\":\"失败\",\"data\":{\"endabled\":\"无异常数据，数据为空\"}}";
                }
                log.debug("length:	" + count);
            }catch (Exception e) {
                log.error("处理查询现在有多少个需要提醒的外拨任务异常，失败：" + e.getMessage());
            }
            return "{\"code\":\"1\",\"message\":\"成功\",\"data\":{\"endabled\":\"外拨超时提醒成功\"}}";
        } catch (JSONException e) {
            log.error("处理异常，失败：" + e.getMessage());
        }
    }

}